package org.cadet.client.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.cadet.client.bean.BeanUser;
import org.cadet.client.bean.BeanUserFollowed;
import org.cadet.util.model.Constants;
import org.cadet.util.model.DatabaseConnection;

/**
 * @author Nagmani
 * 
 */

public class UserManagement {

	DatabaseConnection objConnection = DatabaseConnection.getInstance();
	
	public void followUser(BeanUserFollowed user) throws SQLException { 
		PreparedStatement pst = objConnection.getDbConnection().prepareStatement(Constants.sqlCommands.followUser);
        pst.setInt(1, user.getCurrentUser());
        pst.setInt(2, user.getFollowUser());
        pst.executeUpdate();
        pst.close();
	}

	public void unfollowUser(BeanUserFollowed user) throws SQLException { 
		PreparedStatement pst = objConnection.getDbConnection().prepareStatement(Constants.sqlCommands.unfollowUser);
        pst.setInt(1, user.getCurrentUser());
        pst.setInt(2, user.getFollowUser());
        pst.executeUpdate();
        pst.close();
	}

	public int getUserIdByName(String userName) throws SQLException, Exception {
		PreparedStatement pst = objConnection.getDbConnection()
				.prepareStatement(
						Constants.sqlCommands.RetrieveUserId);
		pst.setString(1, userName);
		ResultSet rs = pst.executeQuery();
		if (rs.next()) {
			System.out.println(rs.getInt("ID"));
			return rs.getInt("ID");
		} else {
			throw new Exception("No such User Exists!");
		}
	}
	
	public String getDisplayNameById(int userId) throws SQLException, Exception {
		PreparedStatement pst = objConnection.getDbConnection()
				.prepareStatement(
						Constants.sqlCommands.RetrieveDisplayNameById);
		pst.setInt(1, userId);
		ResultSet rs = pst.executeQuery();
		if (rs.next()) {
			System.out.println(rs.getString("display_name"));
			return rs.getString("display_name");
		} else {
			throw new Exception("No such User Exists!");
		}
	}

	public ArrayList<BeanUser> viewUsers() {

		ArrayList<BeanUser> userList = new ArrayList<BeanUser>();
		BeanUser user;
		try {
			PreparedStatement pst = objConnection.getDbConnection()
					.prepareStatement(
							Constants.sqlCommands.RetrieveUsers);
			ResultSet rs = pst.executeQuery();

			while (rs.next()) {
				user = new BeanUser();
				user.setUserId(rs.getInt("ID"));
				user.setUserLogin(rs.getString("user_name"));
				user.setDisplayName(rs.getString("display_name"));
				user.setUserPassword(rs.getString("user_pass"));
				user.setUserRegistrationTime(rs.getString("user_registered"));
				user.setUserStatus(rs.getString("user_status"));
				userList.add(user);
			}

		} 
		catch (Exception e) {
		} 
		finally {
		}
		return userList;
	}
	
	public String FollowedIds(int userId) throws Exception{
		PreparedStatement pst1 = objConnection.getDbConnection()
				.prepareStatement(
						Constants.sqlCommands.RetrieveFollowedUserIds);
		pst1.setInt(1, userId);
		ResultSet rs1 = pst1.executeQuery();
		String followedIds = "0";
			while (rs1.next()) {
				followedIds = followedIds + "," + Integer.toString(rs1.getInt("follow_user"));
			}
			
		return followedIds;
	}

	public ArrayList<BeanUser> viewUnfollowedUsers(int userId) throws Exception{
		ArrayList<BeanUser> userList = new ArrayList<BeanUser>();
		BeanUser user;
		String followedIds = FollowedIds(userId);
		Statement statement = objConnection.getDbConnection().createStatement();
		ResultSet rs = statement.executeQuery(Constants.sqlCommands.RetrieveUnfollowedUsers+"("+followedIds+")");
		while (rs.next()) {
				user = new BeanUser();
				user.setUserId(rs.getInt("ID"));
				user.setUserLogin(rs.getString("user_name"));
				user.setDisplayName(rs.getString("display_name"));
				user.setUserPassword(rs.getString("user_pass"));
				user.setUserRegistrationTime(rs.getString("user_registered"));
				user.setUserStatus(rs.getString("user_status"));
				userList.add(user);
			}
		return userList;
	}

	public ArrayList<BeanUser> viewFollowedUsers(int userId) throws Exception{
		ArrayList<BeanUser> userList = new ArrayList<BeanUser>();
		BeanUser user;
		String followedIds = FollowedIds(userId);
		Statement statement = objConnection.getDbConnection().createStatement();
		ResultSet rs = statement.executeQuery(Constants.sqlCommands.RetrieveFollowedUsers+"("+followedIds+")");
		while (rs.next()) {
				user = new BeanUser();
				user.setUserId(rs.getInt("ID"));
				user.setUserLogin(rs.getString("user_name"));
				user.setDisplayName(rs.getString("display_name"));
				user.setUserPassword(rs.getString("user_pass"));
				user.setUserRegistrationTime(rs.getString("user_registered"));
				user.setUserStatus(rs.getString("user_status"));
				userList.add(user);
			}
		return userList;
	}

	public BeanUser viewUserByUserId(int userId)
			throws SQLException {
		PreparedStatement pst = objConnection.getDbConnection()
				.prepareStatement(
						Constants.sqlCommands.RetrieveUserByUserID);
		pst.setInt(1, userId);
		ResultSet rs = pst.executeQuery();
		BeanUser user = new BeanUser();
		if (rs.next()) {
			user = new BeanUser();
			user.setUserId(rs.getInt("ID"));
			user.setUserLogin(rs.getString("user_name"));
			user.setDisplayName(rs.getString("display_name"));
			user.setUserPassword(rs.getString("user_pass"));
			user.setUserRegistrationTime(rs.getString("user_registered"));
			user.setUserStatus(rs.getString("user_status"));
		}
		return user;
	}

}
